package com.oha.laporrayelchurro.service;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.oha.laporrayelchurro.commons.Util;
import com.oha.laporrayelchurro.dao.PartidoDao;
import com.oha.laporrayelchurro.dao.PronosticoDao;
import com.oha.laporrayelchurro.model.Partido;
import com.oha.laporrayelchurro.model.Pronostico;

/**
 * Excel reader
 * @author oscarha
 *
 */
public class ExcelService {
	
	private static Logger log4j = Logger.getLogger(ExcelService.class);
	
	private enum TareaEnum{
		CREAR_PRONOSTICOS_USR,
		CONSTANTES_WEB,
		ELIMINAR_GENERAR_MASTER
	}
	
	/**
	 * 
	 * @param arg
	 */
	public static void main(String[] arg){
		TareaEnum tarea = TareaEnum.ELIMINAR_GENERAR_MASTER;
		switch(tarea){
		case CREAR_PRONOSTICOS_USR:
			xlsToH2();
			break;
		case CONSTANTES_WEB:
			strData();
			break;
		case ELIMINAR_GENERAR_MASTER:
			eliminarGenerarMaster();
			break;
		}
//		marcadores();
//		horarios();
	}
	
	/**
	 * Elimina la tabla partido
	 * Crea la tabla partido
	 * Lee y salva los partidos del  excel Master.xls
	 * Elimina el pronostico del usuario master (id10)
	 * Carga el pronostico
	 * Genera la K de MASTER_LIST
	 */
	private static void eliminarGenerarMaster(){
		PartidoService partidoService = new PartidoService();
		partidoService.resetTable();
		String xlsMaster = Util.getUsrDir()+"/data/Master.xls";
		
		List<Partido> pList = partidoService.getPartidos(xlsMaster);
		PartidoDao parDao = new PartidoDao();
		for(Partido par: pList){
			parDao.save(par);
		}
		PronosticoService pService = new PronosticoService();
		PronosticoDao proDao = new PronosticoDao();
		proDao.delete(10);//El usuario 10 es master
		List<Pronostico> pronosticoList = pService.getPronostico(10, xlsMaster);
		for(Pronostico pro:pronosticoList){
			proDao.save(pro);
		}
		generateK("MASTER_LIST", 10);
		
	}
	
	/**
	 * 
	 */
	private static void strData(){
		log4j.info("strData...");
		String[] var = new String[]{"", "ALBERTO_PORRA", "MARIO_PORRA", "JAVI_PORRA", "JC_PORRA", "PERI_PORRA", "DAVIDB_PORRA", "OSCAR_PORRA", "SESE_PORRA", "MANU_PORRA","MASTER_LIST"}; 
		for(int i=10; i>=1; i--){
			generateK(var[i], i);
		}
		log4j.info("strData.OK.");
	}
	
	/**
	 * 
	 * @param var
	 * @param idUsurio
	 * @param tipo
	 */
	private static void generateK(String var, int idUsuario){
		Integer[] tipos = new Integer[]{16,8,4,3,2,1};
		log4j.info("\n\npublic static final String "+var+"="); 
		log4j.info("\n//nusuario:"+idUsuario);
		PronosticoDao proDao = new PronosticoDao();
		for(int tipo:tipos){
			String data = proDao.strData(idUsuario, tipo);
			data+= (tipo!=1 ? ".concat(":";");
			log4j.info("\n//tipo:"+tipo);
			log4j.info("\n"+data);
		}
	}
	
	/**
	 * 
	 * @param loadPartidos
	 */
	private static void xlsToH2(){
		log4j.info("xlsToH2...");
		String[] fileStr = new String[]{
				"/data/Alberto.xls"
				,"/data/Mario.xls"
				,"/data/Javi.xls"
				,"/data/JC.xls"
				,"/data/Peri.xls"
				,"/data/DavidB.xls"
				,"/data/Oscar.xls"
				,"/data/Sese.xls"
				,"/data/Manu.xls"
		};
		
		PronosticoService pService = new PronosticoService();
		String usrDir = Util.getUsrDir();
		PronosticoDao proDao = new PronosticoDao();
		for(int i=0; i<fileStr.length; i++){
			List<Pronostico> pronosticoList = pService.getPronostico(i+1, usrDir.concat(fileStr[i]));
			pService.eliminarPronostico(i+1);
			for(Pronostico pro:pronosticoList){
				proDao.save(pro);
			}
		}

		log4j.info("xlsToH2.OK.");
	}
	
	/**
	 * 
	 */
	private static void marcadores(){
		ExcelService xlss= new ExcelService();
		File dir = new File("C:/oha/doc/mundial/data/");
		File[] xlsFs = dir.listFiles();
		for(File xls:xlsFs){
			log4j.info("====================================");
			log4j.info("====================================");
			log4j.info(xls.getName());
			log4j.info("====================================");
			log4j.info("====================================");
			List<String> pList = xlss.getP(xls);
			for(String str:pList){
				log4j.info(str);
			}
		}
	}
	
	/**
	 * 
	 * @param rowIni
	 * @param rowEnd
	 * @param colIni
	 * @param colEnd
	 * @param sheet
	 * @return
	 */
	private List<Date> getHorario(int rowIni, int rowEnd, int colIni, int colEnd, int rowstep, Sheet sheet){
		List<Date> dataList = new ArrayList<Date>();
		for(int nrow =rowIni; nrow<rowEnd; nrow+=rowstep){
			Row row = sheet.getRow(nrow);
			for(int col=colIni; col<colEnd; col++){
				Cell cell = row.getCell(col);
				int cellType = row.getCell(col).getCellType();
				if(cellType==1 || cellType==2){//String
					dataList.add(cell.getDateCellValue());
				}
			}
		}
		return dataList;
	}
	
	/**
	 * Obtiene datos del Excel
	 * @return
	 */
	public List<String> getP(File xls){
		List<String> dataList = new ArrayList<String>();
		try{
			Workbook wb = WorkbookFactory.create(xls);
			
			Sheet sheet = wb.getSheetAt(0);
			dataList.add("========================");
			dataList.add("-- 16vos --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(12,75,5,9, sheet)));
			dataList.add("========================");
			dataList.add("-- 8vos --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(13, 44, 22, 25, sheet)));
			dataList.add("========================");
			dataList.add("-- 4tos --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(46, 61, 22, 25, sheet)));
			dataList.add("========================");
			dataList.add("-- Semi --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(63, 69, 22, 25, sheet)));
			dataList.add("========================");
			dataList.add("-- 3er lugar --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(71, 74, 22, 25, sheet)));
			dataList.add("========================");
			dataList.add("-- Final --");
			dataList.add("========================");
			dataList.addAll(toStrList(getXlsData(77, 79, 22, 25, sheet)));
		}catch(InvalidFormatException ife){
			log4j.error("InvalidFormatException.getP.", ife);
		} catch (IOException ioe) {
			log4j.error("IOException.getP.", ioe);
		}
		return dataList;
	}
	
	
	/**
	 * 
	 * @param dataList
	 * @return
	 */
	private List<String> toStrList(List<pg> dataList){
		List<String> strList = new ArrayList<String>();
		for(int i=0; i<dataList.size(); i+=2){
			strList.add(dataList.get(i).toString()+":"+dataList.get(i+1).toString());
		}
		return strList;
	}
	
	/**
	 * 
	 * @param rowIni
	 * @param rowEnd
	 * @param colIni
	 * @param colEnd
	 * @param sheet
	 * @return
	 */
	private static List<pg> getXlsData(int rowIni, int rowEnd, int colIni, int colEnd, Sheet sheet){
		List<pg> dataList = new ArrayList<pg>();
		List<Partido> partidoList = new ArrayList<Partido>();
		List<Pronostico> pronosticoList = new ArrayList<Pronostico>();
		for(int nrow =rowIni; nrow<rowEnd; nrow ++){
			Row row = sheet.getRow(nrow);
			String p = null;
			Integer g = null;
			Partido partido = new Partido();
			Pronostico pronostico = new Pronostico();
			for(int col=colIni; col<colEnd; col++){
				Cell cell = row.getCell(col);
				int cellType = row.getCell(col).getCellType();
				if(col==4){
					partido.setHora(cell.getDateCellValue());
				}else if(col==5 && (cellType==1 || cellType==2)){//String
					partido.setPais1(cell.getStringCellValue());
				}else if(col==6){
					pronostico.setGoles1(((Double)cell.getNumericCellValue()).intValue());
				}else if(col==7){
					pronostico.setGoles2(((Double)cell.getNumericCellValue()).intValue());
				}else if(col==8){
					partido.setPais2(cell.getStringCellValue());
				}
			}
			partidoList.add(partido);
			pronosticoList.add(pronostico);
		}
		return dataList;
	}
	
	
	/**
	 * 
	 * @param sheet
	 * @param savePartido
	 * @return
	 */
	private static List<Pronostico> getPronostico16(Sheet sheet, int idUsuario, int nPartido){
		int rowIni=12;
		int rowEnd=75;
		int colIni=4;
		int colEnd=9;
		int grupo=0;
		List<Pronostico> pronosticoList = new ArrayList<Pronostico>();
		for(int nrow =rowIni; nrow<rowEnd; nrow ++){
			Row row = sheet.getRow(nrow);
			Pronostico pronostico = new Pronostico();
			for(int col=colIni; col<colEnd; col++){
				Cell cell = row.getCell(col);
				int cellType = row.getCell(col).getCellType();
				if(col==4){

				}else if(col==5 && (cellType==1 || cellType==2)){//String

				}else if(col==6){
					pronostico.setGoles1(((Double)cell.getNumericCellValue()).intValue());
				}else if(col==7){
					pronostico.setGoles2(((Double)cell.getNumericCellValue()).intValue());
				}else if(col==8){

				}
			}
			grupo++;
			pronostico.setIdUsuario(idUsuario);
			pronostico.setIdPartido(nPartido++);
			pronosticoList.add(pronostico);
			if(grupo>=6){
				grupo=0;
				nrow+=2;
			}
		}
		return pronosticoList;
	}
	
	/**
	 * 
	 * @author oscarha
	 *
	 */
	private static class pg{
		public String p;
		public Integer g;
		
		
		public String toString() {
			return p.concat(" ").concat(g.toString());
		}
	}
	
}
